﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace 养老金支付系统
{
    class SqlHelper
    {
        private static string sqlserver = Properties.Settings.Default.SQLSERVER;
        /// <summary>
        /// 数据库连接字符串(已ASE加密)，位于App.config
        /// </summary>
        private static string connectionString = string.Format("Data Source={0};Initial Catalog=PPS;Persist Security Info=True;User ID=sa;Password=442443819", sqlserver);
        /// <summary>
        /// sql查询超时时间（单位秒，默认值30）
        /// </summary>
        private static int timeout = 100;
        //public static string connectionString = PDS.Properties.Settings.Default.connString;
        /// <summary>
        /// 执行增删改，返回受影响行数
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="p">参数列表</param>
        /// <returns>返回存储过程return(int)返回值</returns>
        public static int exeNonQuery(string sql, params SqlParameter[] p)
        {
            SqlConnection sqlConn = new SqlConnection(connectionString);
            try
            {
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.CommandTimeout = timeout;
                sqlCmd.Parameters.AddRange(p);
                sqlConn.Open();
                return sqlCmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                sqlConn.Close();
            }
        }
        /// <summary>
        /// 执行存储过程，获取return(int)返回值
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="p">参数列表</param>
        /// <returns>返回存储过程return(int)返回值</returns>
        public static int exeProcReturn(string sql, params SqlParameter[] p)
        {
            SqlConnection sqlConn = new SqlConnection(connectionString);
            try
            {
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandTimeout = timeout;
                sqlCmd.Parameters.AddRange(p);
                sqlCmd.Parameters.Add("@return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sqlConn.Open();
                sqlCmd.ExecuteNonQuery();
                int ret = (int)sqlCmd.Parameters["@return"].Value;
                return ret;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                sqlConn.Close();
            }
        }
        /// <summary>
        /// 执行查询带参的sql语句，返回数据表（DataTable）
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="p">参数列表或参数</param>
        /// <returns>返回数据表（DataTable）</returns>
        public static DataTable getDataTable(string sql, params SqlParameter[] p)
        {
            SqlConnection sqlConn = new SqlConnection(connectionString);
            try
            {
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.CommandTimeout = timeout;
                sqlCmd.Parameters.AddRange(p);
                sqlConn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                return dt;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                sqlConn.Close();
            }
        }
        /// <summary>
        /// 执行查询sql语句，返回第一列的数据到数组（Object[]）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static object[] getFirstColumn(string sql, params SqlParameter[] p)
        {
            SqlConnection sqlConn = new SqlConnection(connectionString);
            try
            {
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.CommandTimeout = timeout;
                sqlCmd.Parameters.AddRange(p);
                sqlConn.Open();
                SqlDataReader dr = sqlCmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                List<object> list = new List<object>();
                foreach (DataRow row in dt.Rows)
                {
                    list.Add(row[0]);
                }
                return list.ToArray();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                sqlConn.Close();
            }
        }
        /// <summary>
        /// 获取第一行第一列的值(Object)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static object getScalar(string sql, params SqlParameter[] p)
        {
            SqlConnection sqlConn = new SqlConnection(connectionString);
            try
            {
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.CommandType = CommandType.Text;
                sqlCmd.CommandTimeout = timeout;
                sqlCmd.Parameters.AddRange(p);
                sqlConn.Open();
                return sqlCmd.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                sqlConn.Close();
            }
        }
        /// <summary>
        /// 拷贝DataTable到数据库对应的表
        /// </summary>
        /// <param name="dt"></param>
        public static void DataTableCopyToDataBase(DataTable dt)
        {
            SqlBulkCopy sqlBC = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
            try
            {
                sqlBC.DestinationTableName = dt.TableName;
                sqlBC.BulkCopyTimeout = timeout;
                sqlBC.WriteToServer(dt);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                sqlBC.Close();
            }
        }
        /// <summary>
        /// DataTable分页
        /// </summary>
        /// <param name="dtAll">DataTable对象</param>
        /// <param name="pageNo">页码号</param>
        /// <param name="pageSize">每页行数</param>
        /// <returns>分页后的DataTable</returns>
        public static DataTable getOnePageDataTable(DataTable dtAll, int pageNo, int pageSize)
        {
            int dtAllRowsCount = dtAll.Rows.Count;
            int pageCount = (dtAllRowsCount / pageSize) + (dtAllRowsCount % pageSize > 0 ? 1 : 0);//获取总页数
            int currentPage = pageNo;
            currentPage = (currentPage > pageCount ? pageCount : currentPage);//如果PageNo过大，则较正PageNo=PageCount
            currentPage = (currentPage <= 0 ? 1 : currentPage);//如果PageNo<=0，则改为首页
            //----克隆表结构到新表
            DataTable onePageDataTable = dtAll.Clone();
            //----取出1页数据到新表
            int rowBegin = (currentPage - 1) * pageSize;
            int rowEnd = currentPage * pageSize;
            rowEnd = (rowEnd > dtAllRowsCount ? dtAllRowsCount : rowEnd);
            for (int i = rowBegin; i <= rowEnd - 1; i++)
            {
                DataRow newRow = onePageDataTable.NewRow();
                DataRow oldRow = dtAll.Rows[i];
                foreach (DataColumn column in dtAll.Columns)
                {
                    newRow[column.ColumnName] = oldRow[column.ColumnName];
                }
                onePageDataTable.Rows.Add(newRow);
            }
            return onePageDataTable;
        }
        /// <summary>
        /// 获取DataTable分页后总页数
        /// </summary>
        /// <param name="dtAll">DataTable对象</param>
        /// <param name="pageSize">每页行数</param>
        /// <returns>总页数</returns>
        public static int getDataTablePageCount(DataTable dtAll, int pageSize)
        {
            int dtAllRowsCount = dtAll.Rows.Count;
            return (dtAllRowsCount / pageSize) + (dtAllRowsCount % pageSize > 0 ? 1 : 0);
        }
    }
}
